package tt.dz.biz.dao.common;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.annotation.Resource;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import redis.clients.jedis.exceptions.JedisConnectionException;
import tt.dz.base.common.entity.ParamRequiredException;
import tt.dz.base.redis.entity.RedisOptionException;
import tt.dz.base.redis.service.RedisService;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.common.BusinessException;
import tt.dz.common.DzConstants;
import tt.dz.common.DzConstants.bizSysCode;
import tt.dz.common.DzErrorCode;
import tt.dz.common.DzErrorCode.CommonErrorCode;
import tt.dz.common.DzErrorCode.ExceptionErrorCode;
import tt.dz.common.LogUtil;
import tt.dz.vo.CityVO;
import tt.dz.vo.CountryVO;
import tt.dz.vo.DistrictVO;
import tt.dz.vo.ProvinceVO;
import tt.dz.vo.object.Node;

@Repository("commonDAO")
public class CommonDAO
  extends BaseDAO
{
  @Resource(name="commonDAOHelpHandler")
  private CommonDAOHelpHandler commonDAOHelpHandler;
  public static final String BIZ_DISTRICT_NODE_KEY = "DISTRICT_NODE";
  public static final String BIZ_COUNTRY_NODE_KEY = "COUNTRY_NODE";
  public static final String REDIS_DISTRICT_NODE_KEY = DzConstants.REDIS_PRE_PUBLIC_KEY + "DISTRICT_NODE";
  @Resource(name="tt.dz.base.redis.service")
  private RedisService redisService;
  private static final String LIST_ALL_COUNTRY = "select * from MS_COUNTRY_CODE";
  private static final String LIST_ALL_PROVINCE = "select * from MS_PROVINCE_CODE";
  private static final String LIST_ALL_CITY = "select * from MS_CITY_CODE";
  private static final String LIST_ALL_DISTRICT = "select * from MS_DISTRICT_CODE";
  private static final String SELECT_DB_CURRENT_TIME = "select CURRENT_TIMESTAMP ";
  private static final String UPDATE_SYS_CUR_STATE = " update ms_sys_current_state set state_code=:stateCode where sys_code=:sysCode ";
  private static final String SELECT_CURRENT_SYS_STATE = "select  state_code as stateCode  from  ms_sys_current_state  where sys_code=:sysCode";
  private static final String LIST_NEXT_CITY = "select a.province_code ,a.province_name, b.city_code, b.city_name from ms_province_code a, ms_city_code b where a.province_code=b.province_code and a.province_code=:districtCode";
  private static final String LIST_NEXT_DISTRICT = "select a.city_code,a.city_name ,b.district_code,b.district_name from ms_city_code a,ms_district_code b where a.city_code =b.city_code and a.city_code=:districtCode";
  private static final String QUERY_DISTRICT = "select district_code,district_name from ms_district_code where district_code=:districtCode";
  
  public List<CountryVO> listAllCountry()
  {
    return queryForList("select * from MS_COUNTRY_CODE", null, CountryVO.class);
  }
  
  public List<ProvinceVO> listAllProvince()
  {
    return queryForList("select * from MS_PROVINCE_CODE", null, ProvinceVO.class);
  }
  
  public List<CityVO> listAllCity()
  {
    return queryForList("select * from MS_CITY_CODE", null, CityVO.class);
  }
  
  public List<DistrictVO> listAllDistrict()
  {
    return queryForList("select * from MS_DISTRICT_CODE", null, DistrictVO.class);
  }
  
  public Date getDBCurrentTime()
  {
    Map<String, ?> paramMap = null;
    try
    {
      return (Date)this.jdbcTemplate.queryForObject("select CURRENT_TIMESTAMP ", paramMap, Date.class);
    }
    catch (EmptyResultDataAccessException emptye)
    {
      LogUtil.debug("查询不到任何记录select CURRENT_TIMESTAMP ");
      return new Date();
    }
    catch (IncorrectResultSizeDataAccessException imcorrecte)
    {
      LogUtil.debug("查询的记录数大于1select CURRENT_TIMESTAMP ");
    }
    return new Date();
  }
  
  public String getCurrentSysState(String sysCode)
  {
    MapSqlParameterSource paramMap = new MapSqlParameterSource();
    paramMap.addValue("sysCode", sysCode);
    try
    {
      return (String)this.jdbcTemplate.queryForObject("select  state_code as stateCode  from  ms_sys_current_state  where sys_code=:sysCode", paramMap, String.class);
    }
    catch (EmptyResultDataAccessException emptye)
    {
      LogUtil.error("查询不到任何记录select  state_code as stateCode  from  ms_sys_current_state  where sys_code=:sysCode");
      return null;
    }
    catch (IncorrectResultSizeDataAccessException imcorrecte)
    {
      LogUtil.error("查询的记录数大于1select  state_code as stateCode  from  ms_sys_current_state  where sys_code=:sysCode");
    }
    return null;
  }
  
  public int updateSysCurrentState(String stateCode)
    throws Exception
  {
    MapSqlParameterSource paramMap = new MapSqlParameterSource();
    paramMap.addValue("stateCode", stateCode);
    paramMap.addValue("sysCode", DzConstants.bizSysCode.MAIN_SERVER.value());
    try
    {
      return save(" update ms_sys_current_state set state_code=:stateCode where sys_code=:sysCode ", paramMap);
    }
    catch (Exception e)
    {
      LogUtil.error("更新系统当前状态异常", e);
      throw e;
    }
  }
  
  public JSONObject listNextDistrictByDistrictCode(String districtCode, String level)
    throws Exception
  {
    JSONObject result = new JSONObject();
    try
    {
      Pattern countryPattern = Pattern.compile("^[1-9]{2}$");
      
      Pattern provincePattern = Pattern.compile("^[1-9]{2}[0]{4}$");
      
      Pattern cityPattern = Pattern.compile("^[1-9][0-9]{3}[0]{2}$");
      Matcher countryMatcher = countryPattern.matcher(districtCode);
      Matcher provinceMatcher = provincePattern.matcher(districtCode);
      Matcher cityMatcher = cityPattern.matcher(districtCode);
      boolean countryBoolean = countryMatcher.matches();
      boolean provinceBoolean = provinceMatcher.matches();
      boolean cityBoolean = cityMatcher.matches();
      

      Node redisnode = (Node)this.redisService.get(REDIS_DISTRICT_NODE_KEY, Node.class);
      Node node;
      if (null != redisnode)
      {
        List<Node> subNodes;
        if (countryBoolean)
        {
          this.commonDAOHelpHandler.validateLevel("0", level);
          if (districtCode.equals(redisnode.getCode()))
          {
            result.put("code", redisnode.getCode());
            result.put("name", redisnode.getName());
            result.put("level", redisnode.getLevel());
            result.put("isLeaf", redisnode.getIsLeaf());
            
            subNodes = redisnode.getSubNodes();
            for (Node node2 : subNodes) {
              node2.setSubNodes(null);
            }
            result.put("subCodeList", subNodes);
          }
        }
        else
        {
//          List<Node> subNodes;
          if (provinceBoolean)
          {
            this.commonDAOHelpHandler.validateLevel("1", level);
            for (Node provinceNode : redisnode.getSubNodes()) {
              if (districtCode.equals(provinceNode.getCode()))
              {
                result.put("code", provinceNode.getCode());
                result.put("name", provinceNode.getName());
                result.put("level", provinceNode.getLevel());
                result.put("isLeaf", provinceNode.getIsLeaf());
                
                subNodes = provinceNode.getSubNodes();
                for (Iterator localIterator2 = subNodes.iterator(); localIterator2.hasNext();)
                {
                  node = (Node)localIterator2.next();
                  
                  node.setSubNodes(null);
                }
                result.put("subCodeList", subNodes);
                break;
              }
            }
          }
          else if (cityBoolean)
          {
            this.commonDAOHelpHandler.validateLevel("2", level);
            

            Node provinceNode = new Node();
            String provinceCode = districtCode.substring(0, 2) + "0000";
            for (Node provinceNodeTemp : redisnode.getSubNodes()) {
              if (provinceCode.equals(provinceNodeTemp.getCode()))
              {
                provinceNode = provinceNodeTemp;
                break;
              }
            }
            for (Node cityNode : provinceNode.getSubNodes()) {
              if (districtCode.equals(cityNode.getCode()))
              {
                result.put("code", cityNode.getCode());
                result.put("name", cityNode.getName());
                result.put("level", cityNode.getLevel());
                result.put("isLeaf", cityNode.getIsLeaf());
                result.put("subCodeList", cityNode.getSubNodes());
                break;
              }
            }
          }
          else
          {
            this.commonDAOHelpHandler.validateLevel("3", level);
            

            String provinceCode = districtCode.substring(0, 2) + "0000";
            
            String cityCode = districtCode.substring(0, 4) + "00";
            Node provinceNode = new Node();
            Node cityNode = new Node();
            for (Node provinceNodeTemp : redisnode.getSubNodes()) {
              if (provinceCode.equals(provinceNodeTemp.getCode()))
              {
                provinceNode = provinceNodeTemp;
                break;
              }
            }
            for (Node cityNodeTemp : provinceNode.getSubNodes()) {
              if (cityCode.equals(cityNodeTemp.getCode()))
              {
                cityNode = cityNodeTemp;
                break;
              }
            }
            if (null != cityNode.getSubNodes()) {
              for (Node districtNode : cityNode.getSubNodes()) {
                if (districtCode.equals(districtNode.getCode()))
                {
                  result.put("code", districtNode.getCode());
                  result.put("name", districtNode.getName());
                  result.put("level", districtNode.getLevel());
                  result.put("isLeaf", districtNode.getIsLeaf());
                  result.put("subCodeList", null);
                  break;
                }
              }
            }
          }
        }
      }
      else
      {
        MapSqlParameterSource paramMap = new MapSqlParameterSource();
        paramMap.addValue("districtCode", districtCode);
        JSONArray jsonArray=new JSONArray();;
        if (("0".equals(level)) || (countryBoolean))
        {
          Object countryList = queryForList("select * from MS_COUNTRY_CODE", null, CountryVO.class);
          
          List<ProvinceVO> provinceList = queryForList("select * from MS_PROVINCE_CODE", null, ProvinceVO.class);
          if ((null != countryList) && (!((List)countryList).isEmpty()))
          {
            result.put("code", ((CountryVO)((List)countryList).get(0)).getCountryCode());
            result.put("name", ((CountryVO)((List)countryList).get(0)).getCountryName());
          }
          result.put("level", Integer.valueOf(0));
          result.put("isLeaf", Integer.valueOf(0));
          if ((null != provinceList) && (!provinceList.isEmpty()))
          {
            jsonArray = new JSONArray();
            for (ProvinceVO provinceVO : provinceList)
            {
              JSONObject jsonTemp = new JSONObject();
              jsonTemp.put("provinceCode", provinceVO.getProvinceCode());
              jsonTemp.put("provinceName", provinceVO.getProvinceName());
              ((JSONArray)jsonArray).add(jsonTemp);
            }
            result.put("subCodeList", jsonArray);
          }
        }
        else if (("1".equals(level)) || (provinceBoolean))
        {
        	 List<Map<String, Object>> citylist = queryForList("select a.province_code ,a.province_name, b.city_code, b.city_name from ms_province_code a, ms_city_code b where a.province_code=b.province_code and a.province_code=:districtCode", paramMap);
          if ((null != citylist) && (!((List)citylist).isEmpty()))
          {
            result.put("code", ((Map)((List)citylist).get(0)).get("provinceCode"));
            result.put("name", ((Map)((List)citylist).get(0)).get("provinceName"));
            result.put("level", Integer.valueOf(1));
            result.put("isLeaf", Integer.valueOf(0));
//            JSONArray jsonArray = new JSONArray();
            for (Map<String, Object> map : citylist) {
				
              JSONObject jsonTemp = new JSONObject();
              jsonTemp.put("cityCode", map.get("cityCode"));
              jsonTemp.put("cityName", map.get("cityName"));
              jsonArray.add(jsonTemp);
            }
            result.put("subCodeList", jsonArray);
          }
        }
        else if (("2".equals(level)) || (cityBoolean))
        {
        	List<Map<String, Object>>  districtList = queryForList("select a.city_code,a.city_name ,b.district_code,b.district_name from ms_city_code a,ms_district_code b where a.city_code =b.city_code and a.city_code=:districtCode", paramMap);
          if ((null != districtList) && (!((List)districtList).isEmpty()))
          {
            result.put("code", ((Map)((List)districtList).get(0)).get("cityCode"));
            result.put("name", ((Map)((List)districtList).get(0)).get("cityName"));
            result.put("level", Integer.valueOf(2));
            result.put("isLeaf", Integer.valueOf(0));
            for (Map<String, Object> map : districtList) {
              JSONObject jsonTemp = new JSONObject();
              jsonTemp.put("districtCode", map.get("districtCode"));
              jsonTemp.put("districtName", map.get("districtName"));
              jsonArray.add(jsonTemp);
            }
            result.put("subCodeList", jsonArray);
          }
        }
        else
        {
          DistrictVO districtVO = new DistrictVO();
          districtVO.setDistrictCode(districtCode);
          districtVO = (DistrictVO)queryForObject("select district_code,district_name from ms_district_code where district_code=:districtCode", districtVO, DistrictVO.class);
          if (null != districtVO)
          {
            result.put("code", districtVO.getDistrictCode());
            result.put("name", districtVO.getDistrictName());
            result.put("level", Integer.valueOf(3));
            result.put("isLeaf", Integer.valueOf(1));
          }
        }
      }
    }
    catch (JedisConnectionException e)
    {
      DzErrorCode.ExceptionErrorCode ec = DzErrorCode.ExceptionErrorCode.COMMON_EXCEPTION;
      throw new BusinessException(ec.value(), ec.getDesc(), "连接redis异常");
    }
    catch (Exception e)
    {
      DzErrorCode.ExceptionErrorCode ec = DzErrorCode.ExceptionErrorCode.COMMON_EXCEPTION;
      throw new BusinessException(ec.value(), ec.getDesc(), "系统异常");
    }
    if (result.isEmpty())
    {
      DzErrorCode.CommonErrorCode e = DzErrorCode.CommonErrorCode.DISTRICTCODE_ERROR;
      throw new BusinessException(e.value(), e.getDesc(), "区域代码districtCode不正确");
    }
    return result;
  }
  
  public Node searchAllClassificationAndAssembly()
    throws Exception
  {
    Node node = null;
    try
    {
      node = (Node)this.redisService.get(REDIS_DISTRICT_NODE_KEY, Node.class);
      if (null != node) {
        return node;
      }
      node = new Node();
      

      Map<String, CountryVO> countryMap = this.commonDAOHelpHandler.getCountryMap();
      
      Map<String, List<ProvinceVO>> provinceMap = this.commonDAOHelpHandler.getProvinceMap();
      
      Map<String, List<CityVO>> cityMap = this.commonDAOHelpHandler.getCityMap();
      
      Map<String, List<DistrictVO>> districtMap = this.commonDAOHelpHandler.getDistrictMap();
      
      List<Node> subTmpList = null;
      Map<String, List<Node>> subCityNodeMap = new HashMap();
      String cityCode = null;
      Map.Entry<String, List<DistrictVO>> cityEntity = null;
      for (Iterator<Map.Entry<String, List<DistrictVO>>> it = districtMap.entrySet().iterator(); it.hasNext();)
      {
        cityEntity = (Map.Entry)it.next();
        cityCode = (String)cityEntity.getKey();
        
        subTmpList = this.commonDAOHelpHandler.convertNodeByDistrict((List)cityEntity.getValue());
        subCityNodeMap.put(cityCode, subTmpList);
      }
      Map<String, List<Node>> subProviceNodeMap = new HashMap();
      String provinceCode = null;
      Map.Entry<String, List<CityVO>> provinceEntity = null;
      for (Iterator<Map.Entry<String, List<CityVO>>> it = cityMap.entrySet().iterator(); it.hasNext();)
      {
        provinceEntity = (Map.Entry)it.next();
        provinceCode = (String)provinceEntity.getKey();
        
        subTmpList = this.commonDAOHelpHandler.convertNodeByCity((List)provinceEntity.getValue(), subCityNodeMap);
        subProviceNodeMap.put(provinceCode, subTmpList);
      }
      Map<String, List<Node>> subCountryNodeMap = new HashMap();
      String conuntryCode = null;
      Map.Entry<String, List<ProvinceVO>> countryEntity = null;
      for (Iterator<Map.Entry<String, List<ProvinceVO>>> it = provinceMap.entrySet().iterator(); it.hasNext();)
      {
        countryEntity = (Map.Entry)it.next();
        conuntryCode = (String)countryEntity.getKey();
        
        subTmpList = this.commonDAOHelpHandler.convertNodeByProvince((List)countryEntity.getValue(), subProviceNodeMap);
        subCountryNodeMap.put(conuntryCode, subTmpList);
      }
      node.setCode(((CountryVO)countryMap.get("COUNTRY_NODE")).getCountryCode());
      node.setName(((CountryVO)countryMap.get("COUNTRY_NODE")).getCountryName());
      node.setLevel("0");
      node.setIsLeaf("0");
      node.setSubNodes(subTmpList);
      

      this.redisService.save(REDIS_DISTRICT_NODE_KEY, node);
    }
    catch (JedisConnectionException e)
    {
      DzErrorCode.ExceptionErrorCode ec = DzErrorCode.ExceptionErrorCode.COMMON_EXCEPTION;
      throw new BusinessException(ec.value(), ec.getDesc(), "连接redis异常");
    }
    catch (ParamRequiredException e)
    {
      DzErrorCode.ExceptionErrorCode ec = DzErrorCode.ExceptionErrorCode.COMMON_EXCEPTION;
      throw new BusinessException(ec.value(), ec.getDesc(), "区域数据存redis时发生参数异常");
    }
    catch (RedisOptionException e)
    {
      DzErrorCode.ExceptionErrorCode ec = DzErrorCode.ExceptionErrorCode.COMMON_EXCEPTION;
      throw new BusinessException(ec.value(), ec.getDesc(), "区域数据存redis时发生redis选项异常");
    }
    catch (Exception e)
    {
      DzErrorCode.ExceptionErrorCode ec = DzErrorCode.ExceptionErrorCode.COMMON_EXCEPTION;
      throw new BusinessException(ec.value(), ec.getDesc(), "全查询区域代码异常");
    }
    return node;
  }
}
